using System;
using System.Data;

using BizElements.Core;

namespace BizElements.BusinessLayer
{
    /// <summary>
    /// Generic search engine which accesses all databases supported by <b>BizElements</b> and provides meta-data for all search elements.
    /// </summary>
	[Serializable]
	public sealed class SearchEngine : ISearchEngine
	{
		#region Fields.

        DbColumnCollection keyFields;
		SearchFieldCollection searchFields = new SearchFieldCollection();
		DisplayItemCollection displayItems = new DisplayItemCollection();
		int top;
		IDbTable fromTable;
		RelationBucket relations = new RelationBucket();
		OrderByClause sorter = new OrderByClause();		
		SearchCondition filter = new SearchCondition();
		bool sortOnDb = true;
		
        // NonSearialized fields must be saved/pinned before remoting and restored afterwards.
        [NonSerialized]
		PostProcessDelegate postProcess;

		#endregion

		#region Constructors.

        /// <summary>
        /// Initializes a new instance of <b>SearchEngine</b> class.
        /// </summary>
        /// <param name="fromTable">Table that will be searched. Table's primary key used as engine's 
        /// <see cref="PrimaryKeyFields"/>. Additional tables can be joined via <see cref="Relations"/> property.</param>
		public SearchEngine(IDbTable fromTable) 
            : this(fromTable, fromTable.PrimaryKey.ToArray())
		{
		}

        /// <summary>
        /// Initializes a new instance of <b>SearchEngine</b> class.
        /// </summary>
        /// <param name="fromTable">Table that will be searched. Additional tables can be joined via <see cref="Relations"/> property.</param>
        /// <param name="keyFields">Columns used as engine's <see cref="PrimaryKeyFields"/>.</param>
		public SearchEngine(IDbTable fromTable, IDbColumn[] keyFields)
		{				
			this.fromTable = fromTable;
			this.keyFields = new DbColumnCollection();
			foreach (IDbColumn field in keyFields)
				this.keyFields.Add(field);
		}

		#endregion

        #region AddSortExpression.

        /// <summary>
        /// Adds user defined sort expression for the retrieved values.
        /// </summary>
        /// <param name="field">FieldPermissions by whitch the the results are ordered.</param>
        /// <param name="ascending">Specifies whether the values in the specified fields should be sorted in 
        /// ascending order, from lowest value to highest value.</param>
        public void AddSortExpression(string field, bool ascending)
		{
			if (this.sorter == null)
				this.sorter = new OrderByClause();

            DisplayItem selectItem = this.DisplayFields[field];
            bool isDbColumn = (selectItem != null) && (selectItem.ItemType == SqlItemType.Column);
            if (isDbColumn)
            {
                // Rendered as: ORDER BY [table.][field] ASC
                // Required if engine searches through multiple tables which contain columns with same names.
                IDbColumn column = (IDbColumn)selectItem.Item;
                this.sorter.Add(column, ascending);
            }
            else
            {
                // Rendered as: ORDER BY [field] ASC
                this.sorter.Add(field, ascending);
            }
        }

        #endregion

        #region ClearSortExpressions.

        /// <summary>
        /// Clears user-defined sort expressions.
        /// </summary>
        public void ClearSortExpressions()
		{
			if (this.sorter == null)
				this.sorter = new OrderByClause();

			this.sorter.Clear();
        }

        #endregion

        #region GetSortExpressions.

        /// <summary>
        /// Gets user defined sort expressions for the retrieved values.
        /// </summary>
        /// <returns>Array of sort expressions.</returns>
        public SearchEngineSortExpression[] GetSortExpressions()
        {
            if (this.sorter == null)
                this.sorter = new OrderByClause();

            SearchEngineSortExpression[] allExpressions = new SearchEngineSortExpression[this.sorter.Count];
            int idxExpression = 0;
            foreach (SortExpression expression in this.sorter.GetAllSortExpressions())
            {
                string field = (expression.Field != null) ? expression.Field.Alias : expression.ColumnName;
                allExpressions[idxExpression] = new SearchEngineSortExpression(field, expression.Ascending);
                idxExpression++;
            }

            return allExpressions;
        }

        #endregion

        #region FetchByPkValues.

        /// <summary>
        /// Fetches data which matches the given key values.
        /// </summary>
        /// <param name="pkValues">Key values given in the order as specified in the <see cref="PrimaryKeyFields"/> property.</param>
        /// <returns>Data which matched the given criteria.</returns>
        public DataView FetchByPkValues(object[,] pkValues)
		{
            // Due to serialization/deserialization process during remoting post-process delegate (which is a non-serializable member may be lost). Pin the reference in a local variable.
            PostProcessDelegate postProcessBeforeRemoting = this.postProcess;
            DataTable data = RpcHelper.InvokeMethod<DataTable, object[,]>(this, this.DbFetchByPkValues, pkValues);
            this.postProcess = postProcessBeforeRemoting;

            DataView view = data.DefaultView;
			if (this.sorter != null  &&  this.sorter.Count > 0)	
				view.Sort = this.sorter.GetDataViewSortExpression();

			if (this.postProcess != null)
				view = postProcess(view);
			
			return view;
		}

        private DataTable DbFetchByPkValues(object[,] pkValues)
        {
            if (ArrayUtil.IsNullOrEmpty(this.keyFields))
                throw new InvalidOperationException("Key fields haven't been defined for the search engine.");

            // Create SELECT.
            SelectStatement select = new SelectStatement(this.fromTable);
            select.Top = this.top;
            select.SelectList = CreateSelectList();
            select.Relations = this.relations;

            // Filter. Eg for composite PK: "(countryId = 1 AND townCode = 50) OR (countryId = 2 AND townCode = 10)".
            for (int recordIdx = 0; recordIdx < pkValues.Length; recordIdx++)
            {
                SearchCondition currKey = new SearchCondition();
                for (int pkPart = 0; pkPart < this.keyFields.Count; pkPart++)
                    currKey.And(PredicateFactory.Compare(this.keyFields[pkPart], "=", pkValues[recordIdx, pkPart]));

               select.Where.Or(currKey);
            }

            return select.Execute();
        }

        #endregion

        #region Search.

        private void TryToSubstitueNonColumnDisplayItemsInSorterWithColumns()
        {
            // Try to substitute non-column display items with columns. Users could have defined sort expressions
            // before display fields. In that case ORDER BY clause will contain column names instead of columns
            // with aliases. If multiple tables with same column names, error would occur.
            if (this.sorter == null)
                this.sorter = new OrderByClause();
            
            OrderByClause sorterWithColumns = new OrderByClause();
            SortExpression[] mixedSortExpressions = this.sorter.GetAllSortExpressions();
            foreach (SortExpression expression in mixedSortExpressions)
            {
                IDbColumn column = null;
                if (expression.Field != null)
                {
                    column = expression.Field;
                }
                else
                {
                    DisplayItem selectItem = this.DisplayFields[expression.ColumnName];
                    bool isDbColumn = (selectItem != null) && (selectItem.ItemType == SqlItemType.Column);
                    if (isDbColumn)
                        column = (IDbColumn)selectItem.Item;
                }

                if (column != null)
                    sorterWithColumns.Add(column, expression.Ascending);
                else
                    sorterWithColumns.Add(expression.ColumnName, expression.Ascending);
            }

            this.sorter = sorterWithColumns;
        }

        /// <summary>
        /// Creates a dynamic filter and retrieves data. Calls PostProcess delegate if provided when it's done.
        /// </summary>
        /// <returns>Data that matches the user defined criteria.</returns>
        public DataView Search()
        {
            TryToSubstitueNonColumnDisplayItemsInSorterWithColumns();

            // Due to serialization/deserialization process during remoting post-process delegate (which is a non-serializable member) may be lost. Pin the reference in a local variable.
            PostProcessDelegate postProcessBeforeRemoting = this.postProcess;
            DataTable data = RpcHelper.InvokeMethod<DataTable>(this, this.DbSearch);
            this.postProcess = postProcessBeforeRemoting;

            DataView view = data.DefaultView;
            if (!this.sortOnDb && this.sorter != null && this.sorter.Count > 0)
                view.Sort = this.sorter.GetDataViewSortExpression();

            if (this.DataFetched != null)
                DataFetched(this, new EventArgs<DataView>(view));

            if (this.postProcess != null)
                view = postProcess(view);

            if (this.DataReady != null)
                DataReady(this, new EventArgs<DataView>(view));

            return view;
        }

        private DataTable DbSearch()
        {
            SelectStatement select = CreateSelectStatement(true);
            select.Distinct = this.Distinct;
            DataTable data = select.Execute();
            foreach (IBindableField displayField in this.DisplayFields)
            {
                DataColumn col = data.Columns[displayField.BindingName];
                if (col != null)
                    col.Caption = displayField.Caption;
            }

            return data;
        }

        private SelectStatement CreateSelectStatement(bool orderByRequired)
        {
            // Can't set Distinct here because this method is used in many places.
            SelectStatement select = new SelectStatement(this.fromTable);
            select.Top = this.top;
            select.SelectList = CreateSelectList();
            select.Relations = this.relations;

            // Filter.
            if (this.filter != null && this.filter.IsEmpty == false)
                select.Where.Add(this.filter);

            SearchCondition dynamicFilter = CreateDynamicFilter();
            if (dynamicFilter != null)
            {
                if (select.Where.IsEmpty)
                    select.Where.Add(dynamicFilter);
                else
                    select.Where.And(dynamicFilter);
            }

            // Sorter.
            if (orderByRequired && this.sortOnDb && this.sorter != null && this.sorter.Count > 0)
                select.OrderBy = this.sorter;

            return select;
        }

        private SelectItemCollection CreateSelectList()
        {
            SelectItemCollection list = new SelectItemCollection();
            for (int i = 0; i < this.displayItems.Count; i++)
                list.Add(this.displayItems[i]);

            foreach (IDbColumn col in this.keyFields)
            {
                if (SelectListContains(list, col) == false)
                    list.Add(col);
            }

            for (int i = 0; i < this.searchFields.Count; i++)
            {
                IDbColumn currField = this.searchFields[i].Field;
                if (SelectListContains(list, currField) == false)
                    list.Add(currField);
            }

            return list;
        }

        private bool SelectListContains(SelectItemCollection list, IDbColumn field)
        {
            foreach (SelectItem item in list)
            {
                if (item.ItemType == SqlItemType.Column)
                {
                    if (((IDbColumn)item.Item).Alias == field.Alias)
                        return true;
                }
            }

            return false;
        }

        private SearchCondition CreateDynamicFilter()
        {            
            SearchCondition dynamicFilter = new SearchCondition();

            // Ensure that a field whose AddWithOr is set to true is not a first predicate in the 
            // search condition. First add predicates which have to be combined with AND.
            foreach (SearchField field in this.searchFields)
            {
                if (!field.AddWithOr)
                {
                    object predicateOrSubCondition = field.CreatePredicate();
                    if (predicateOrSubCondition != null)
                    {
                        PredicateBase predicate = predicateOrSubCondition as PredicateBase;
                        if (predicate != null)
                        {
                            dynamicFilter.And(predicate);
                        }
                        else
                        {
                            SearchCondition subCondition = predicateOrSubCondition as SearchCondition;
                            if (subCondition != null && !subCondition.IsEmpty)
                                dynamicFilter.And(subCondition);
                        }
                    }
                }
            }

            // AND-ed fields are stricter than OR-ed fields.
            // Example: Status=1 (AND), FirstName='John' (OR), LastName='Smith' (OR)
            // This is now interpreted as: WHERE Status=1 AND (FirstName='John' OR LastName='Smith')
            // Before it was interpreted as: WHERE Status=1 OR FirstName='John' OR LastName='Smith'
            SearchCondition orFields = new SearchCondition();
            bool noAndFields = dynamicFilter.IsEmpty;            

            foreach (SearchField field in this.searchFields)
            {
                if (field.AddWithOr)
                {
                    object predicateOrSubCondition = field.CreatePredicate();
                    if (predicateOrSubCondition != null)
                    {
                        PredicateBase predicate = predicateOrSubCondition as PredicateBase;
                        if (predicate != null)
                        {
                            orFields.Or(predicate);
                        }
                        else
                        {
                            SearchCondition subCondition = predicateOrSubCondition as SearchCondition;
                            if (subCondition != null && !subCondition.IsEmpty)
                                orFields.Or(subCondition);
                        }
                    }
                }
            }

            if (!orFields.IsEmpty)
            {
                if (noAndFields)
                    dynamicFilter = orFields;
                else
                    dynamicFilter.And(orFields);
            }

            if (dynamicFilter.IsEmpty == false)
                return dynamicFilter;
            else
                return null;
        }

        #endregion

        #region Count.

        /// <summary>
        /// Creates a dynamic filter and counts all records in the data-source that match the filter criteria.
        /// </summary>
        /// <returns>Total number of records that match the criteria.</returns>
        /// <remarks>Usefull when the number of records retrieved by <see cref="Search"/> method has been limited
        /// by <see cref="MaxNumberOfRecordsReturned"/> property.</remarks>
        public int Count()
        {
            // Due to serialization/deserialization process during remoting post-process delegate (which is a non-serializable member may be lost). Pin the reference in a local variable.
            PostProcessDelegate postProcessBeforeRemoting = this.postProcess;
            int count = RpcHelper.InvokeMethod<int>(this, this.DbCount);
            this.postProcess = postProcessBeforeRemoting;
            return count;
        }

        private int DbCount()
        {
            SelectStatement select = CreateSelectStatement(false);
            select.Top = -1;
            select.SelectList.Clear();
            select.OrderBy.Clear();
            if (this.Distinct)
            {
                if (this.keyFields.Count != 1)
                    throw new InvalidOperationException("This search engine implementation requires that exactly one key field is defined to execute distinct count. Number of key fields: " + this.keyFields.Count);

                select.SelectList.Add(AggregateFunctionFactory.Count(this.keyFields[0], true, "recordCount"));
            }
            else
            {
                select.SelectList.Add(AggregateFunctionFactory.Count("recordCount"));
            }

            DataTable dtRecordCount = select.Execute();

            try { return (dtRecordCount.Rows[0][0] != DBNull.Value ? Convert.ToInt32(dtRecordCount.Rows[0][0]) : 0); }
            catch { return 0; }
        }

        #endregion

        #region BizElements specific properties.

        /// <summary>
        /// Gets or sets the value which specifies that only unique rows can appear in the result set.
        /// </summary>
        /// <remarks><para><b>Performance.</b> Be careful when using DISTINCT if retrieving a lot of large data 
        /// as the database performance may be considerably degraded.</para>
        /// <para><b>Restrictions.</b> Some RDBMSs don't support DISTINCT for all field types. Eg. the text, 
        /// ntext, or image data type cannot be selected as DISTINCT on SQL Server 7 and 2000.</para>
        /// <para><b>NULL values.</b> For the DISTINCT keyword, null values are considered to be duplicates 
        /// of each other. When DISTINCT is included in a SELECT statement, only one NULL is returned 
        /// in the results, regardless of how many null values are encountered.</para></remarks>
        public bool Distinct { get; set; }

        /// <summary>Gets the table that will be searched. Additional tables can be joined via <see cref="Relations"/> property.</summary>
        public IDbTable FromTable
        {
            get { return this.fromTable; }
        }

        /// <summary>
        /// Gets or sets the <b>SearchCondition</b> which restricts the rows returned.
        /// </summary>
        /// <remarks>User-created dynamic filter is appended to this filter.</remarks>
        public SearchCondition Filter
        {
            get { return this.filter; }
            set { this.filter = value; }
        }

        /// <summary>
        /// Gets or sets the <b>RelationBucket</b> that contains the relations used by this engine. 
        /// Required when the engine retrieves columns from multiple tables. 
        /// </summary>
        public RelationBucket Relations
        {
            get { return this.relations; }
            set { this.relations = value; }
        }

        /// <summary>
        /// Gets or sets the <b>OrderByClause</b> which specifies the sort for the result set. 
        /// </summary>
        public OrderByClause Sorter
        {
            get { return this.sorter; }
            set { this.sorter = value; }
        }

        #endregion

        #region ISearchEngine properties.

        /// <summary>
        /// Gets the collection of fields which are to be retrieved and displayed in the search results.
        /// </summary>
        public DisplayItemCollection DisplayFields
        {
            get { return this.displayItems; }
        }

        IBindableFieldCollection ISearchEngine.DisplayFields
        {
            get { return this.displayItems; }
        }

        /// <summary>
        /// Gets or sets the maximum numbers of records to retrieve from the data-source.
        /// </summary>
        public int MaxNumberOfRecordsReturned
        {
            get { return this.top; }
            set { this.top = value; }
        }

        /// <summary>
        /// Sets the method which can modify the fetched data.
        /// </summary>
        /// <remarks>This can be used to add a computed column in the fetched DataTable, or reformat fetched data.</remarks>
        public PostProcessDelegate PostProcess
        {
            set { this.postProcess = value; }
        }

        /// <summary>
        /// Gets the primary key fields which uniquely identify a data-source record.
        /// </summary>
        public IBindableFieldCollection PrimaryKeyFields
        {
            get
            {
                DisplayItemCollection pkFields = new DisplayItemCollection();
                foreach (IDbColumn field in this.keyFields)
                    pkFields.Add(field, "");

                return pkFields;
            }
        }

        /// <summary>
        /// Gets the collection of fields whose begin and end values users can change to create a dynamic filter.
        /// </summary>
		public SearchFieldCollection SearchFields
		{
			get {return this.searchFields;}
		}

		ISearchEngineFieldCollection ISearchEngine.SearchFields
		{
			get {return this.searchFields;}
		}

        /// <summary>
        /// Gets or sets the value which specifies whether sorting will take place on the data-source or in engine after the data has been fetched.
        /// </summary>
		public bool SortOnDb
		{
			get {return this.sortOnDb;}
			set {this.sortOnDb = value;}
		}

		#endregion		

        /// <summary>Sets key fields.</summary>
        /// <param name="keyFields">Columns used as engine's <see cref="PrimaryKeyFields"/>.</param>
        public void SetPrimaryKeyFields(IDbColumn[] keyFields)
        {
            this.keyFields = new DbColumnCollection();
            foreach (IDbColumn field in keyFields)
                this.keyFields.Add(field);
        }

        #region Events.

        /// <summary>An event that occurs immedieately after the data are fetched but before the <see cref="PostProcess"/> delegate is called.</summary>
        public event EventHandler<EventArgs<DataView>> DataFetched;

        /// <summary>An event that occurs after <see cref="PostProcess"/> delegate, i.e. just before the engine returns the data to client.</summary>
        public event EventHandler<EventArgs<DataView>> DataReady;

        #endregion
	}       
}
